<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Bonus_helper
 *
 * @author tejomurti
 */
class Bonus_helper {
    //put your code here
    
    /**
     * output json
     *      default: object
     *              array_list
     */
    private $json_output;
    
    public function __construct($output) {
        $this->json_output=$output;
        
    }
    
    /**
     * helper bonus
     * @param int $network_id
     * @param string $type
     *              - default: summary
     *              - today
     */
    public function summary_bonus($network_id,$type='')
    {
        $summary = vo_network::get_bonus_acc($network_id);
        $today = vo_network::get_bonus_by_netgrow($network_id, date('Y-m-d'));
        $today['gen_node_bonus'] = Yii::app()->db->createCommand("SELECT (IFNULL(SUM(gen_node_netgrow_value), 0) * " . Yii::app()->params['bonus']['gen_node'] . ") AS gen_node_bonus FROM sp_gen_node_netgrow WHERE gen_node_netgrow_network_id = '" . $network_id . "' AND gen_node_netgrow_date = '" . date('Y-m-d') . "'")->queryScalar();
        $today['match_bonus'] = Yii::app()->db->createCommand("SELECT (IFNULL(SUM(match_netgrow_value), 0) * " . Yii::app()->params['bonus']['match'] . ") AS match_bonus FROM sp_match_netgrow WHERE match_netgrow_network_id = '" . $network_id . "' AND match_netgrow_date = '" . date('Y-m-d') . "'")->queryScalar();
        $today['total_bonus'] += ($today['gen_node_bonus']+$today['match_bonus']);
        //$ewallet_income = vo_network::get_ewallet_income_by_date(Yii::app()->user->getState('network_id'), date('Y-m-d'));
        $ewallet_income = vo_network::get_ewallet_income($network_id);
        $ewallet_cash = $ewallet_noncash = $ewallet_products = $ewallet_voucher = array(
            'sponsor' => 0,
            'extra_sponsor' => 0,
            'gen_node' => 0,
            'gen_node_products' => 0,
            'gen_node_extra' => 0,
            'match' => 0,
            'gen_match' => 0,
            'matrix_node' => 0,
            'board_level' => 0,
            'board_fly' => 0,
            'total_bonus' => 0,
            'sponsor_upgrade' => 0,
        );
        
        $data=array();
        if ($ewallet_income != false) {
            
            foreach ($ewallet_income as $row_ewallet_income) {
                $ewallet_cash[$row_ewallet_income['ewallet_income_bonus_name']] = $row_ewallet_income['ewallet_income_cash'];
                $ewallet_noncash[$row_ewallet_income['ewallet_income_bonus_name']] = $row_ewallet_income['ewallet_income_noncash'];
                $ewallet_products[$row_ewallet_income['ewallet_income_bonus_name']] = $row_ewallet_income['ewallet_income_products'];
                $ewallet_voucher[$row_ewallet_income['ewallet_income_bonus_name']] = $row_ewallet_income['ewallet_income_voucher'];

                $ewallet_cash['total_bonus'] += $row_ewallet_income['ewallet_income_cash'];
                $ewallet_noncash['total_bonus'] += $row_ewallet_income['ewallet_income_noncash'];
                $ewallet_products['total_bonus'] += $row_ewallet_income['ewallet_income_products'];
                $ewallet_voucher['total_bonus'] += $row_ewallet_income['ewallet_income_voucher'];
            }
            
            $config_bonus=array(
                
                
                'bonus'=>array('sponsor'=>'sponsor','extra_sponsor'=>'extra_sponsor','gen_node_products'=>'royalti_produk','gen_node'=>'prestasi_distributor',
                                    'match'=>'couple','gen_match'=>'sharing_couple','matrix_node'=>'spillover',
                               'gen_node_extra'=>'prestasi_factory','board_level'=>'level_board','board_fly'=>'cycle_board','sponsor_upgrade'=>'drp','total'=>'total'),
                
                );
           
            
            //EXTRACT BONUS
            foreach($config_bonus['bonus'] AS $key=>$row)
            {
                if($type=='today')
                {
                    if($key!='sponsor_upgrade')
                    {
                        $data[][$row]=$today[$key.'_bonus'];
                    }
                            
                }
                else
                {
                    switch($key)
                    {
                        case 'total':
                        $data[$row]['summary']=$summary[$key.'_bonus'];
                        $data[$row]['ewallet_cash']=$ewallet_cash[$key.'_bonus'];
                        $data[$row]['ewallet_products']=$ewallet_products[$key.'_bonus'];
                        $data[$row]['ewallet_voucher']=$ewallet_voucher[$key.'_bonus']; 
                            break;
                        default:
                        $data[$row]['summary']=$summary[$key.'_bonus'];
                        $data[$row]['ewallet_cash']=$ewallet_cash[$key];
                        $data[$row]['ewallet_products']=$ewallet_products[$key];
                        $data[$row]['ewallet_voucher']=$ewallet_voucher[$key]; 
                            break;
                    }    
                }
                    
            }
        }

        if($this->json_output=='array')
        {
            if($type=='today')
            {
                return $data;
            }
            else
            {
               // $data=array();
                if(!empty($data))
                {
                    foreach($data AS $key=>$row)
                    {
                       $result[][$key]=$row; 
                    }
                }
                
                    return $result;
            }
        }
        else
        {
            return $data;
        }
    }
    
    public function network_binary_report($network_id)
    {
        $results=array();
        $summary['wait_left'] = vo_network::get_last_wait_node_bonus($network_id, 'left');
        $summary['wait_right'] = vo_network::get_last_wait_node_bonus($network_id, 'right');
        $summary['total_node_left'] = function_lib::get_one("SUM(netgrow_bonus_node_left)", "sys_netgrow_bonus", "netgrow_bonus_network_id = '" . $network_id . "'");
        $summary['total_node_right'] = function_lib::get_one("SUM(netgrow_bonus_node_right)", "sys_netgrow_bonus", "netgrow_bonus_network_id = '" . $network_id . "'");
        $summary['total_match'] = function_lib::get_one("SUM(netgrow_bonus_match)", "sys_netgrow_bonus", "netgrow_bonus_network_id = '" . $network_id . "'");
        $summary['total_sponsor'] = function_lib::get_one("SUM(sponsor_netgrow_value)", "sp_sponsor_netgrow", "sponsor_netgrow_network_id = '" . $network_id . "'");
        $summary['total_flushout'] = function_lib::get_one("COUNT(*)", "sys_netgrow_bonus", "netgrow_bonus_network_id = '" . $network_id . "' AND netgrow_bonus_match >= 12");

        $date_start_default = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 7, date("Y")));
        //$date_end_default = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 1, date("Y")));
        $date_end_default = date("Y-m-d");

        $date_start = (!empty($_POST['date_start'])) ? $_POST['date_start'] : $date_start_default;
        $date_end = (!empty($_POST['date_end'])) ? $_POST['date_end'] : $date_end_default;

        //$data = vo_network::get_netgrow_master(Yii::app()->user->getState('network_id'), $date_start, $date_end);
        $data = vo_network::get_netgrow_bonus($network_id, $date_start, $date_end);
        
        if($this->json_output=='array')
        {
             $results[]['total_sponsor']=$summary['total_sponsor'];
             $results[]['total_titik_kiri']=$summary['total_node_left'];
             $results[]['total_titik_kanan']=$summary['total_node_right'];
             $results[]['total_pasangan']=$summary['total_match'];
             $results[]['total_hangus']=$summary['total_flushout'];
             $results[]['menunggu_kiri']=$summary['wait_left'];
             $results[]['menunggu_kanan']=$summary['wait_right'];
        }
        else
        {
            $results['total_sponsor']=$summary['total_sponsor'];
            $results['total_titik_kiri']=$summary['total_node_left'];
            $results['total_titik_kanan']=$summary['total_node_right'];
            $results['total_pasangan']=$summary['total_match'];
            $results['total_hangus']=$summary['total_flushout'];
            $results['menunggu_kiri']=$summary['wait_left'];
            $results['menunggu_kanan']=$summary['wait_right'];
        }
       
        
     
        return $results;
    }
    
    
    //UNTUK MENAMPILKAN NAMA FACTORY
    public function factory_name($value)
    {
        /*
         *  UMHJ = Haji
            FACT = Umroh
            FPRO =  Produk
            ELHJ = Haji
            EDUC = Edukasi
            */
            switch(strtolower($value))
            {
                case 'umhj':
                $result='Haji';    
                    break;
                case 'fact':
                $result='Umroh';    
                    break;
                case 'fpro':
                $result='Produk';    
                    break;
                case 'elhj':
                $result='Haji';    
                    break;
                case 'educ':
                $result='Edukasi';    
                    break;
                default:
                $result=$value;    
                    break;
            }
            return $result;
    }
    
    public function statement_bonus($network_id)
    {
        $date_start_default = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 30, date("Y")));
        $date_end_default = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 1, date("Y")));

        $date_start = (!empty($_POST['date_start'])) ? $_POST['date_start'] : $date_start_default;
        $date_end = (!empty($_POST['date_end'])) ? $_POST['date_end'] : $date_end_default;

        $date_diff = ((strtotime($date_end) - strtotime($date_start)) / 86400) + 1;
        //$network_id = Yii::app()->user->getState('network_id');

        $data = array();

        $total = array(
            'bonus' => 0,
            'index' => 0,
            'subtotal' => 0,
            'adm' => 0,
            'total' => 0,
        );
        for ($i = 0; $i < $date_diff; $i++) {
            $arr_date = explode("-", $date_start);
            $date = date("Y-m-d", mktime(0, 0, 0, $arr_date[1], $arr_date[2] + $i, $arr_date[0]));
            $rs_bonus_cut_percent = Yii::app()->db->createCommand("SELECT * FROM sys_bonus_cut_daily WHERE bonus_cut_daily_date = '" . $date . "'")->queryRow();
            $index_percent = $rs_bonus_cut_percent['bonus_cut_daily_index'];
            $adm_percent = $rs_bonus_cut_percent['bonus_cut_daily_adm'];

            $sponsor = function_lib::get_one("sponsor_bonus_log_value", "sp_sponsor_bonus_log", "sponsor_bonus_log_network_id = '" . $network_id . "' AND sponsor_bonus_log_date = '" . $date . "'");
            $extra_sponsor = function_lib::get_one("extra_sponsor_bonus_log_value", "sp_extra_sponsor_bonus_log", "extra_sponsor_bonus_log_network_id = '" . $network_id . "' AND extra_sponsor_bonus_log_date = '" . $date . "'");
            $gen_node = function_lib::get_one("SUM(gen_node_bonus_log_value)", "sp_gen_node_bonus_log", "gen_node_bonus_log_network_id = '" . $network_id . "' AND gen_node_bonus_log_date = '" . $date . "'");
            $gen_node_products = function_lib::get_one("SUM(gen_node_products_bonus_log_value)", "sp_gen_node_products_bonus_log", "gen_node_products_bonus_log_network_id = '" . $network_id . "' AND gen_node_products_bonus_log_date = '" . $date . "'");
            $gen_node_extra = function_lib::get_one("SUM(gen_node_extra_bonus_log_value)", "sp_gen_node_extra_bonus_log", "gen_node_extra_bonus_log_network_id = '" . $network_id . "' AND gen_node_extra_bonus_log_date = '" . $date . "'");
            $match = function_lib::get_one("match_bonus_log_value", "sp_match_bonus_log", "match_bonus_log_network_id = '" . $network_id . "' AND match_bonus_log_date = '" . $date . "'");
            $gen_match = function_lib::get_one("SUM(gen_match_bonus_log_value)", "sp_gen_match_bonus_log", "gen_match_bonus_log_network_id = '" . $network_id . "' AND gen_match_bonus_log_date = '" . $date . "'");
            $matrix_node = function_lib::get_one("SUM(matrix_node_bonus_log_value)", "sp_matrix_node_bonus_log", "matrix_node_bonus_log_network_id = '" . $network_id . "' AND matrix_node_bonus_log_date = '" . $date . "'");
            $board_level = function_lib::get_one("SUM(board_level_bonus_log_value)", "sp_board_level_bonus_log", "board_level_bonus_log_network_id = '" . $network_id . "' AND board_level_bonus_log_date = '" . $date . "'");
            $board_fly = function_lib::get_one("SUM(board_fly_bonus_log_value)", "sp_board_fly_bonus_log", "board_fly_bonus_log_network_id = '" . $network_id . "' AND board_fly_bonus_log_date = '" . $date . "'");
            $sponsor_upgrade = function_lib::get_one("SUM(sponsor_upgrade_bonus_log_value)", "sp_sponsor_upgrade_bonus_log", "sponsor_upgrade_bonus_log_network_id = '" . $network_id . "' AND sponsor_upgrade_bonus_log_date = '" . $date . "'");
            $bonus = $sponsor + $extra_sponsor + $gen_node + $match + $gen_match + $matrix_node + $board_level + $board_fly + $gen_node_products + $gen_node_extra + $sponsor_upgrade;
            $index = Yii::app()->db->createCommand("SELECT SUM(ewallet_cut_index) AS cut_index FROM sys_ewallet_cut WHERE ewallet_cut_network_id = '" . $network_id . "' AND ewallet_cut_date = '" . $date . "'")->queryScalar();
            $subtotal = $bonus - $index;
            $adm = Yii::app()->db->createCommand("SELECT SUM(ewallet_cut_adm) AS cut_adm FROM sys_ewallet_cut WHERE ewallet_cut_network_id = '" . $network_id . "' AND ewallet_cut_date = '" . $date . "'")->queryScalar();
            $total_transfer = $subtotal - $adm;

            if ($bonus > 0) {
                $data[$i]['date'] = $date;
                $data[$i]['bonus'] = $bonus;
                $data[$i]['index_percent'] = $index_percent;
                $data[$i]['index'] = $index;
                $data[$i]['subtotal'] = $subtotal;
                $data[$i]['adm_percent'] = $adm_percent;
                $data[$i]['adm'] = $adm;
                $data[$i]['total'] = $total_transfer;

                $total['bonus'] += $bonus;
                $total['index'] += $index;
                $total['subtotal'] += $subtotal;
                $total['adm'] += $adm;
                $total['total'] += $total_transfer;
            }
        }
        
        $result=array(
            'date_start' => $date_start,
            'date_end' => $date_end,
            'date_diff' => $date_diff,
            'data' => $data,
            'total' => $total,
        );
        
        return $result;
    }
    
    public function history_withdraw($network_id)
    {
        $date_start_default = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 29, date("Y")));
        //$date_end_default = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 1, date("Y")));
        $date_end_default = date("Y-m-d");

        $date_start = (!empty($_POST['date_start'])) ? $_POST['date_start'] : $date_start_default;
        $date_end = (!empty($_POST['date_end'])) ? $_POST['date_end'] : $date_end_default;
        //die("$date_start # $date_end");
        $criteria = new CDbCriteria;

        $criteria->condition = "ewallet_withdrawal_network_id = '" . $network_id . "'
                AND DATE(ewallet_withdrawal_datetime) BETWEEN '" . $date_start . "' AND '" . $date_end . "'";
        //$criteria->condition = "ewallet_log_datetime >= '$date_start 00:00:00'";
        //$criteria->condition = "ewallet_log_datetime <= '$date_end 00:00:00'";
        //$criteria->condition = "ewallet_withdrawal_datetime between '$date_start 00:00:00' AND '$date_end 00:00:00'";
        $criteria->order = "ewallet_withdrawal_datetime ASC";

        $model = sys_ewallet_withdrawal::model()->findAll($criteria);
        
        $saldo=  sys_ewallet::model()->find('ewallet_network_id=:id',array(':id'=>$network_id));
        
       return array(
            'model' => $model,
            'date_start' => $date_start,
            'date_end' => $date_end,
            'saldo' => $saldo,
        );
    }
    
    /*
     * ambil data history upgrade user
     */
    
    public function history_upgrade($network_id, $merge=false)
    {
        $model=new sys_serial_products_user;
        $aktivasi=  sys_member::model()->findByPk($network_id);
        
        $criteria=new CDbCriteria;
        $criteria->addCondition('serial_products_user_network_id='.$network_id);
        $criteria->order='serial_products_user_datetime';
        
        $result_spu=$model->findAll($criteria);
        $aktivasi_arr=array();
        $result=array();
        if(!empty($result_spu))
        {
            $no=1;
            foreach($result_spu AS $row)
            {
                $result[$no]['status']=self::factory_name(substr($row->serial_products_user_serial_products_id, 0,4));
                $result[$no]['tanggal']=$row->serial_products_user_datetime;
                $result[$no]['serial']=$row->serial_products_user_serial_products_id;
            
                $no++;
            }
        }
        
        $results=$result;
        //jika digabungkan datanya
        if($merge)
        {
            $aktivasi_arr[]=array(
                'status'=>'Aktivasi',
                'tanggal'=>$aktivasi->member_join_datetime,
                'serial'=>$aktivasi->member_serial_id,
            );
            
            $results=array_merge($aktivasi_arr,$result);
        }
        
        $dataProvider=new CArrayDataProvider($results,array(
            'keyField'=>'status',
        ));
        
        return array(
            'aktivasi'=>$aktivasi,
            'dataProvider'=>$dataProvider,
            'results'=>$results,
        );
    }
    
    //statement detail
    public function statement_detail($network_id, $date)
    {
        $data['date'] = $date;

        $bonus = array(
            'sponsor' => 0,
            'extra_sponsor' => 0,
            'gen_node' => 0,
            'gen_node_products' => 0,
            'gen_node_extra' => 0,
            'match' => 0,
            'gen_match' => 0,
            'matrix_node' => 0,
            'board_level' => 0,
            'board_fly' => 0,
            'sponsor_upgrade' => 0,
            'total' => 0,
        );
        $cut_index = $cut_adm = $subtotal = array(
            'sponsor' => 0,
            'extra_sponsor' => 0,
            'gen_node' => 0,
            'gen_node_products' => 0,
            'gen_node_extra' => 0,
            'match' => 0,
            'gen_match' => 0,
            'matrix_node' => 0,
            'board_level' => 0,
            'board_fly' => 0,
            'sponsor_upgrade' => 0,
        );
        $total = array(
            'sponsor' => 0,
            'extra_sponsor' => 0,
            'gen_node' => 0,
            'gen_node_products' => 0,
            'gen_node_extra' => 0,
            'match' => 0,
            'gen_match' => 0,
            'matrix_node' => 0,
            'board_level' => 0,
            'board_fly' => 0,
            'sponsor_upgrade' => 0,
            'cut_index' => 0,
            'subtotal' => 0,
            'cut_adm' => 0,
            'total' => 0,
        );
        //$network_id = Yii::app()->user->getState('network_id');
        $rs_bonus_cut_percent = Yii::app()->db->createCommand("SELECT * FROM sys_bonus_cut_daily WHERE bonus_cut_daily_date = '" . $date . "'")->queryRow();
        $percent['index'] = $rs_bonus_cut_percent['bonus_cut_daily_index'];
        $percent['adm'] = $rs_bonus_cut_percent['bonus_cut_daily_adm'];

        $bonus['sponsor'] = function_lib::get_one("sponsor_bonus_log_value", "sp_sponsor_bonus_log", "sponsor_bonus_log_network_id = '" . $network_id . "' AND sponsor_bonus_log_date = '" . $date . "'");
        $bonus['extra_sponsor'] = function_lib::get_one("extra_sponsor_bonus_log_value", "sp_extra_sponsor_bonus_log", "extra_sponsor_bonus_log_network_id = '" . $network_id . "' AND extra_sponsor_bonus_log_date = '" . $date . "'");
        $bonus['gen_node'] = function_lib::get_one("SUM(gen_node_bonus_log_value)", "sp_gen_node_bonus_log", "gen_node_bonus_log_network_id = '" . $network_id . "' AND gen_node_bonus_log_date = '" . $date . "'");
        $bonus['gen_node_products'] = function_lib::get_one("SUM(gen_node_products_bonus_log_value)", "sp_gen_node_products_bonus_log", "gen_node_products_bonus_log_network_id = '" . $network_id . "' AND gen_node_products_bonus_log_date = '" . $date . "'");
        $bonus['gen_node_extra'] = function_lib::get_one("SUM(gen_node_extra_bonus_log_value)", "sp_gen_node_extra_bonus_log", "gen_node_extra_bonus_log_network_id = '" . $network_id . "' AND gen_node_extra_bonus_log_date = '" . $date . "'");
        $bonus['match'] = function_lib::get_one("match_bonus_log_value", "sp_match_bonus_log", "match_bonus_log_network_id = '" . $network_id . "' AND match_bonus_log_date = '" . $date . "'");
        $bonus['gen_match'] = function_lib::get_one("SUM(gen_match_bonus_log_value)", "sp_gen_match_bonus_log", "gen_match_bonus_log_network_id = '" . $network_id . "' AND gen_match_bonus_log_date = '" . $date . "'");
        $bonus['matrix_node'] = function_lib::get_one("SUM(matrix_node_bonus_log_value)", "sp_matrix_node_bonus_log", "matrix_node_bonus_log_network_id = '" . $network_id . "' AND matrix_node_bonus_log_date = '" . $date . "'");
        $bonus['board_level'] = function_lib::get_one("SUM(board_level_bonus_log_value)", "sp_board_level_bonus_log", "board_level_bonus_log_network_id = '" . $network_id . "' AND board_level_bonus_log_date = '" . $date . "'");
        $bonus['board_fly'] = function_lib::get_one("SUM(board_fly_bonus_log_value)", "sp_board_fly_bonus_log", "board_fly_bonus_log_network_id = '" . $network_id . "' AND board_fly_bonus_log_date = '" . $date . "'");
        $bonus['sponsor_upgrade'] = function_lib::get_one("SUM(sponsor_upgrade_bonus_log_value)", "sp_sponsor_upgrade_bonus_log", "sponsor_upgrade_bonus_log_network_id = '" . $network_id . "' AND sponsor_upgrade_bonus_log_date = '" . $date . "'");
        $total['bonus'] = $bonus['sponsor'] + $bonus['extra_sponsor'] + $bonus['gen_node'] + $bonus['match'] + $bonus['gen_match'] + $bonus['matrix_node'] + $bonus['board_level'] + $bonus['board_fly'] + $bonus['sponsor_upgrade'];

        $sql_bonus_cut = "SELECT 
                    DISTINCT(ewallet_cut_bonus_name) AS bonus_name, 
                    SUM(ewallet_cut_index) AS cut_index, 
                    SUM(ewallet_cut_adm) AS cut_adm 
                    FROM sys_ewallet_cut 
                    WHERE ewallet_cut_network_id = '" . $network_id . "' 
                    AND ewallet_cut_date = '" . $date . "'
                    GROUP BY ewallet_cut_bonus_name";
        $rs_bonus_cut = Yii::app()->db->createCommand($sql_bonus_cut)->queryAll();
        if ($rs_bonus_cut != false) {
            foreach ($rs_bonus_cut as $row) {
                $cut_index[$row['bonus_name']] = $row['cut_index'];
                $subtotal[$row['bonus_name']] = $bonus[$row['bonus_name']] - $row['cut_index'];
                $cut_adm[$row['bonus_name']] = $row['cut_adm'];
                $total[$row['bonus_name']] = $subtotal[$row['bonus_name']] - $row['cut_adm'];
                $total['cut_index'] += $row['cut_index'];
                $total['subtotal'] += $subtotal[$row['bonus_name']];
                $total['cut_adm'] += $row['cut_adm'];
                $total['total'] += $total[$row['bonus_name']];
            }
        }
        
        return array(
            'data' => $data,
            'percent' => $percent,
            'bonus' => $bonus,
            'cut_index' => $cut_index,
            'cut_adm' => $cut_adm,
            'subtotal' => $subtotal,
            'total' => $total,
        );
    }
}

?>
